package org.example.votingsystem.dao;
import org.example.votingsystem.items.YearPeople;
import org.example.votingsystem.util.SqlManege;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class AccountYearPeopleDao {
//    检擦是否有投票记录
    public boolean checkVoteRecord(String id) throws SQLException {
        SqlManege sqlManege = new SqlManege();
        Connection connection = sqlManege.connect();
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(id)\n" +
                "FROM account_yearPeople\n" +
                "WHere id = ?\n" +
                "GROUP BY id ");
        preparedStatement.setString(1, id);
        int counts = 0;
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            counts = resultSet.getInt(1);
        }
        sqlManege.disconnect(connection, preparedStatement);
        return counts>0;
    }
//    链接数据库给counts+1
    public void addCounts(int account,String id) throws SQLException {
        SqlManege sqlManege = new SqlManege();
        Connection connection = sqlManege.connect();
//        票数+1
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE yearpeople\n" +
                "SET counts = counts+1\n" +
                "WHERE account = ?");
        preparedStatement.setInt(1, account);
        preparedStatement.executeUpdate();
        sqlManege.disconnect(connection, preparedStatement);

//        插入投票记录
        Connection connection2 = sqlManege.connect();
        PreparedStatement  preparedStatement2 = connection2.prepareStatement("INSERT INTO account_yearPeople\n" +
                "( id,account,times) \n" +
                "VALUES\n" +
                "(?,?,NOW())");
        preparedStatement2.setString(1, id);
        preparedStatement2.setInt(2, account);
        preparedStatement2.executeUpdate();
        sqlManege.disconnect(connection2, preparedStatement2);
    }
//    查找投票记录
    public ArrayList<YearPeople> findVoteRecord() throws SQLException {
        SqlManege sqlManege = new SqlManege();
        Connection connection = sqlManege.connect();
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT ye.account,ye.name,a.id,a.name AS name2,ay.times\n" +
                "FROM account_yearpeople ay\n" +
                "JOIN account a ON a.id = ay.id\n" +
                "JOIN yearpeople ye ON ye.account = ay.account");
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<YearPeople> yearPeopleList = new ArrayList<>();
        while (resultSet.next()) {
            YearPeople yearPeople = new YearPeople();
            yearPeople.setAccount(resultSet.getInt(1));
            yearPeople.setName(resultSet.getString(2));
            yearPeople.setId(resultSet.getString(3));
            yearPeople.setName2(resultSet.getString(4));
            yearPeople.setTimes(resultSet.getString(5));
            yearPeopleList.add(yearPeople);
        }
        sqlManege.disconnect(connection, preparedStatement);
        return yearPeopleList;
    }
//    删除投票记录 投票数--
    public void deleteVoteRecord(String id,int account) throws SQLException {
        SqlManege sqlManege = new SqlManege();
        Connection connection = sqlManege.connect();
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM account_yearPeople\n" +
                "WHERE id = ?");
        preparedStatement.setString(1, id);
        preparedStatement.executeUpdate();
        sqlManege.disconnect(connection, preparedStatement);

//        投票数--
        Connection connection2 = sqlManege.connect();
        PreparedStatement  preparedStatement2 = connection2.prepareStatement("UPDATE yearpeople\n" +
                "SET counts = counts-1\n" +
                "WHERE account = ?");
        preparedStatement2.setInt(1, account);
        preparedStatement2.executeUpdate();
        sqlManege.disconnect(connection2, preparedStatement2);
    }
}
